<?php

use Core\DB;
use Libs\Fun;

class _db extends base
{
    private $db_filter_str = ['mysql', 'information_schema', 'performance_schema', 'sys']; //忽略包含这些字符的数据库名
    private $tb_filter_str = ['test']; //忽略包含这些字符的表名

    private $alias_host = ['default' => 'defaults']; //主机名的别名(自动生成文件名时使用, 防止出现PHP关键字, 下同)
    private $alias_db = ['default' => 'defaults']; //数据库名的别名
    private $alias_table = ['default' => 'defaults']; //表名的别名

    private $str_replace = [
        '，' => ',',
        '：' => ':', 
        "\r\n" => ' ',
        "\n" => ' ',
        '（' => '(',
        '）' => ')',
    ];

    //自动生成的类文件中, $fields中的字段. 如果需要增加字段, 可以取消注释, 并赋一个别名即可
    private $schema = [
        //'TABLE_CATALOG' => '',
        //'TABLE_SCHEMA' => 'db_name',
        //'TABLE_NAME' => 'table_name',
        'COLUMN_NAME' => 'name',
        //'ORDINAL_POSITION' => '',
        'COLUMN_DEFAULT' => 'default',
        //'IS_NULLABLE' => '',
        'DATA_TYPE' => 'type',
        'CHARACTER_MAXIMUM_LENGTH' => 'max_len',
        //'CHARACTER_OCTET_LENGTH' => '',
        //'NUMERIC_PRECISION' => '',
        //'NUMERIC_SCALE' => '',
        //'DATETIME_PRECISION' => '',
        //'CHARACTER_SET_NAME' => '',
        //'COLLATION_NAME' => '',
        //'COLUMN_TYPE' => 'ctype',
        //'COLUMN_KEY' => 'index', //索引类型 PRI:主键
        //'EXTRA' => '',
        //'PRIVILEGES' => '',
        'COLUMN_COMMENT' => 'comment',
        //'GENERATION_EXPRESSION' => '',
    ];

    private $tab1 = PHP_EOL.'            ';

    public function isExistsStr($str, $arr)
    {
        foreach ($arr as $ignore) {
            if (strpos($str, $ignore) !== false) {
                return true;
            }
        }

        return false;
    }

    //替换特殊字符
    public function replaceChar($str, $rule=[])
    {
        if (empty($rule)) {
            $rule = $this->str_replace;
        }
        return str_replace(array_keys($rule), $rule, $str);
    }

    //保留需要的键, 并改名
    public function arrayFilter($arr, $filter=[])
    {
        $a = [];
        foreach ($arr as $k => $v) {
            if (!empty($filter) && !isset($filter[$k])) {
                continue;
            }

            $key = isset($filter[$k]) ? $filter[$k] : $k;

            $a[$key] = $v;
        }

        return $a;
    }

    //将数组转为标准字符串, 1维数组
    public function fields2str($arr)
    {
        $s = [];
        foreach ($arr as $fieldInfo) {
            $a = [];
            $fieldName = $fieldInfo['name'];
            unset($fieldInfo['name']);
            foreach ($fieldInfo as $k => $v) {
                if ($v == "''") {
                    $value = $v;
                } elseif (strpos($v, "'") === 0 || strpos($v, '"') === 0) {
                    $value = $v;
                } elseif (strpos($v, "'") !== false || strpos($v, '"') !== false) {
                    $value = addslashes($v) ;
                } elseif (is_null($v) || $v == 'NULL') {
                    $value = 'NULL';
                } elseif (is_numeric($v)) {
                    $value = $v;
                } elseif (is_string($v)) {
                    $v = $this->replaceChar($v);
                    $value = "'$v'";
                } else {
                    $value = $v;
                }
    
                $a[] = "'$k' => $value";
            }
            $s[] = "'$fieldName' => [".implode(',', $a).']';
        }
        return "[".$this->tab1. implode(','.$this->tab1, $s).$this->tab1."]";
    }

    public function getDefault($v)
    {
        if ($v == "''") {
            $value = $v;
        } elseif (strpos($v, "'") === 0 || strpos($v, '"') === 0) {
            $value = $v;
        } elseif (strpos($v, "'") > 0 || strpos($v, '"') > 0) {
            $value = addslashes($v) ;
        } elseif (is_null($v) || $v == 'NULL') {
            $value = 'NULL';
        } elseif (is_numeric($v)) {
            $value = $v;
        } elseif (is_string($v)) {
            $vt = strtolower($v);
            if (in_array($vt, ['current_timestamp', 'current_timestamp()'])) {
                $value = 'NULL';
            } else {
                $v = $this->replaceChar($v);
                $value = "'$v'";
            }

        } else {
            $value = $v;
        }
        
        return $value;
    }
    
    public function arr2str($arr, $chunk=5)
    {
        $a = [];
        foreach ($arr as $k => $v) {
            if ($v == "''") {
                $value = $v;
            } elseif (strpos($v, "'") === 0 || strpos($v, '"') === 0) {
                $value = $v;
            } elseif (strpos($v, "'") !== false || strpos($v, '"') !== false) {
                $value = addslashes($v) ;
            } elseif (is_null($v) || $v == 'NULL') {
                $value = 'NULL';
            } elseif (is_numeric($v)) {
                $value = $v;
            } elseif (is_string($v)) {
                $v = $this->replaceChar($v);
                $value = "'$v'";
            } else {
                $value = $v;
            }

            $a[] = "'$k' => $value";
        }
        $list = array_chunk($a, $chunk, true);
        //echo '<pre>'; print_r($list);exit;
        
        $items = [];
        foreach ($list as $item) {
            $items[] = implode(', ', $item);
        }
        return '['.implode(','.PHP_EOL, $items).']';
    }

    //获取可以连接的主机信息
    public function dbHosts()
    {
        $db = new DB();
        $hosts = $db->getHostInfo();
        return array_keys($hosts);
    }

    //获取数据库列表
    public function dbDatabase($host)
    {
        $db = new DB();
        $dbList = $db->getDatabaseList($host);
        sort($dbList);
        return $dbList;
    }

    //获取所有表
    public function dbTables($host, $dbname)
    {
        $db = new DB();
        $tbList = $db->getTableList($host, $dbname);
        return $tbList;
    }

    //获取所有字段
    public function dbFields($host, $dbname, $table)
    {
        $db = new DB();
        return $db->getFieldList($host, $dbname, $table);
    }

    public function getHostName($name)
    {
        $alias = $this->alias_host;
        if (!empty($alias) && isset($alias[$name])) {
            $nm = $alias[$name];
        } else {
            $nm = $name;
        }

        return $nm;
    }

    public function getDbName($name)
    {
        $alias = $this->alias_db;
        if (!empty($alias) && isset($alias[$name])) {
            $nm = $alias[$name];
        } else {
            $nm = $name;
        }

        return $nm;
    }

    public function getTableName($name)
    {
        $alias = $this->alias_table;
        if (!empty($alias) && isset($alias[$name])) {
            $nm = $alias[$name];
        } else {
            $nm = $name;
        }

        return $nm;
    }
    
    //获取各字段的部分信息
    public function getFieldInfo($host, $db, $table)
    {
        $fieldList = $this->dbFields($host, $db, $table);
        
        $fieldInfo = [];
        foreach ($fieldList as $field) {
            //$name = $this->replaceChar($field['COLUMN_NAME'], ['-' => '']); //字段名字中有特殊字符
            $need = $this->arrayFilter($field, $this->schema); //取出部分字段
            $need['default'] = $this->getDefault($need['default']);
            $fieldInfo[$need['name']] = $need;
        }
        
        ksort($fieldInfo);
        
        return $fieldInfo;
    }
    
    //获取表的主键
    public function getTablePk($host, $db, $table)
    {
        $fieldList = $this->dbFields($host, $db, $table);
        $primaryKey = '';
        foreach ($fieldList as $field) {
            if ($field['COLUMN_KEY'] == 'PRI') {
                $primaryKey = $field['COLUMN_NAME'];
                break;
            }
        }
        return $primaryKey;
    }

    //创建类文件, 每个表一个类
    public function make_cache_table()
    {
        //创建host目录
        $hosts = $this->dbHosts();
        foreach ($hosts as $host) {
            $hostName = $this->getHostName($host);
            $hdir = DBPATH . $hostName;
            if (!is_dir($hdir)) {
                mkdir($hdir, 0766, true);
            }
        }
        
        //创建database文件, 写入表,字段信息
        foreach ($hosts as $host) {
            $hostName = $this->getHostName($host);
            $dbs = $this->dbDatabase($host);
            foreach ($dbs as $db) {
                //筛选掉不需要的数据库
                if ($this->isExistsStr($db, $this->db_filter_str) === true) {
                    continue;
                }

                //创建数据库文件夹
                $dbName = $this->getDbName($db);
                $dbDir = DBPATH . $hostName. '/' . $dbName;
                if (!is_dir($dbDir)) {
                    mkdir($dbDir, 0766, true);
                }

                $tables = $this->dbTables($host, $db);
                foreach ($tables as $table) {
                    echo $table.PHP_EOL;

                    //创建表的类文件
                    $tableName = $this->getTableName($table);
                    $file = $dbDir . '/'. $tableName. '.php';
                    $tpl = [];
                    $tpl[] = '<?php';
                    $tpl[] = "namespace DB\\$hostName\\$dbName;";
                    $tpl[] = "use DB\BaseTable;";
                    $tpl[] = "class {$tableName} extends BaseTable {";
                    $tpl[] = "    public static \$host = '$host'; //主机连接名";
                    $tpl[] = "    public static \$database = '$db'; //数据库名";
                    $tpl[] = "    public static \$table_name = '$table'; //表名";

                    $fieldList = $this->dbFields($host, $db, $table);
                    $fields = Fun::sort2DArray($fieldList, 'COLUMN_NAME'); //按字段名排序, 防止多人改动时代码冲突
                    $fieldInfo = [];
                    $primaryKey = ''; //只支持单字段主键索引
                    foreach ($fields as $field) {
                        if ($field['COLUMN_KEY'] == 'PRI') {
                            $primaryKey = $field['COLUMN_NAME'];
                        }
                        //$name = $this->replaceChar($field['COLUMN_NAME'], ['-' => '']); //字段名字中有特殊字符
                        $need = $this->arrayFilter($field, $this->schema);
                        $need['default'] = $this->getDefault($need['default']);
                        $fieldInfo[] = $need;
                    }

                    $fis = $this->fields2str($fieldInfo);

                    $tpl[] ="    //主键索引";
                    $tpl[] ="    public static \$pk = '$primaryKey';";
                    $tpl[] ="    //各字段详细信息";
                    $tpl[] ="    public static \$fields = $fis;";
                    $tpl[] = "   public static function obj() { ";
                    $tpl[] = "       return new self();";
                    $tpl[] = "   }";
                    $tpl[] ="}";
                    
                    file_put_contents($file, implode(PHP_EOL, $tpl));
                }    
            }   
        }
    }
    
    //每个数据库一个类
    public function make_cache_db()
    {
        //创建host目录
        $hosts = $this->dbHosts();
        foreach ($hosts as $host) {
            $hostName = $this->getHostName($host);
            $hdir = DBPATH . $hostName;
            if (!is_dir($hdir)) {
                mkdir($hdir, 0766, true);
            }
        }
        
        //创建database文件, 写入表,字段信息
        foreach ($hosts as $host) {
            $hostName = $this->getHostName($host);
            $dbs = $this->dbDatabase($host);
            foreach ($dbs as $db) {
                //筛选掉不需要的数据库
                if ($this->isExistsStr($db, $this->db_filter_str) === true) {
                    continue;
                }

                //创建数据库文件
                $dbName = $this->getDbName($db);
                $dbFile = DBPATH . $hostName. '/' . $dbName.'.php';
                file_put_contents($dbFile, ''); //清空文件
                
                $tpl = [];
                $tpl[] = '<?php';
                $tpl[] = "namespace DB\\$hostName;";
                $tpl[] = "use DB\BaseTable;";
                $tpl[] = "class {$dbName} extends BaseTable {";

                $tables = $this->dbTables($host, $db);
                foreach ($tables as $table) {
                    echo $table.PHP_EOL;

                    //创建表的类文件
                    $tableName = $this->getTableName($table);
                    $primaryKey = $this->getTablePk($host, $db, $table);
                    
                    $tpl[] = "    public static \${$tableName} = [";
                    $tpl[] ="        'host' => '$host', 'database' => '$db', 'table' => '$table', 'pk' => '$primaryKey',";
                    
                    $fieldInfo = $this->getFieldInfo($host, $db, $table);
                    $fi = $this->fields2str($fieldInfo); //各字段详细信息
                    $tpl[] ="        'fields' => ". $fi.',';
                    
                    $tpl[] ='    ];'.PHP_EOL;
                    
                }
                
                $tpl[] ="}";
                file_put_contents($dbFile, implode(PHP_EOL, $tpl));
            }   
        }
    }
}
